/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package packageDataAccess;

import packageException.ConnectionException;
import java.util.Date;
import java.sql.*;
import java.sql.Connection;
import java.util.Map.*;
import javax.sql.DataSource;
import javax.naming.*;
import packageModel.*;
import packageException.*;
import java.util.ArrayList;
import java.util.GregorianCalendar;

/**
 *
 * @author laurent
 */
public class BricoWebData 
{
    public BricoWebUser getBricowebUser(String userId) throws ConnectionException
    {

        BricoWebUser bricoWebUser;
        
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL = "select * from APP.BRICOWEBUSER where USERID = ?";
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setString(1, userId);
            ResultSet donnees = prepStat.executeQuery();
            donnees.next();

            bricoWebUser = new BricoWebUser(donnees.getString("USERID"),
                                                         donnees.getString("LASTNAME"),
                                                         donnees.getString("FIRSTNAME"),
                                                         donnees.getString("DELIVERYADDRESS"),
                                                         donnees.getString("EMAIL"),
                                                         donnees.getString("PHONENUMBER"),
                                                         donnees.getString("PASSWORD"));
            connection.close();
        }
        catch(Exception e) 
        {
            throw new ConnectionException(e.getMessage().toString());
        }
   
        return bricoWebUser;
    }
    
    public void setBricowebUser(BricoWebUser bU) throws ConnectionException
    {
        
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL = "insert into APP.BRICOWEBUSER(USERID, LASTNAME,FIRSTNAME,DELIVERYADDRESS,EMAIL,PHONENUMBER,PASSWORD)values (?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setString(1, bU.getUserID());
            prepStat.setString(2, bU.getLastName());
            prepStat.setString(3, bU.getFirstName());
            prepStat.setString(4, bU.getDeliveryAddress());
            prepStat.setString(5, bU.getPhoneNumber());
            prepStat.setString(6, bU.getEmail());
            prepStat.setString(7, bU.getPassword());
            prepStat.executeUpdate();   
            
            connection.close();
        }
        catch(Exception e) 
        {
            throw new ConnectionException(e.getMessage());
        } 
    }
    
    public void setBasket(Basket basket) throws ConnectionException
    {
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL = "insert into APP.BASKET(TIMESTAMP, DELIVERED , PAID, USERID)values (?, 0, 0, ?)";
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            Date date = new java.util.Date();
            java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
            prepStat.setTimestamp(1, timestamp);
            prepStat.setString(2, basket.getUser().getUserID());
            prepStat.executeUpdate();
            
            requeteSQL = "select BASKETID from APP.BASKET where TIMESTAMP = ?";
            prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setTimestamp(1, timestamp);
            ResultSet donnees = prepStat.executeQuery();
            donnees.next();
            int basketID = donnees.getInt("BASKETID");
            
            for(ProductsLine prodLine : basket.getHashProductsLine().values())
            { 
                requeteSQL = "insert into APP.PRODUCTHISTORY(UNITARYPRICE, QUANTITY,BASKETID,PRODUCTID)values (?, ?, ?, ?)";
                prepStat = connection.prepareStatement(requeteSQL);
                prepStat.setDouble(1, prodLine.getProduct().getUnitaryPrice());
                prepStat.setInt(2, prodLine.getNbProducts());
                prepStat.setInt(3, basketID);
                prepStat.setInt(4, prodLine.getProduct().getId());
                prepStat.executeUpdate();
            }
            
            connection.close();
        }
        catch(Exception e) 
        {
            throw new ConnectionException(e.getMessage());
        }
    }
        
    public ArrayList<Category> getAllCategories() throws ConnectionException
    {
        ArrayList<Category> listCategories = new ArrayList<Category>();
        
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL = "select * from APP.CATEGORY order by CATEGORYNAME DESC";
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            ResultSet donnees = prepStat.executeQuery();
            
            while(donnees.next())
            {
               listCategories.add(new Category(donnees.getString("CATEGORYNAME"), donnees.getInt("CATEGORYID")));                         
            }   
            
            connection.close();
            
            return listCategories;  
        }
                    
        catch(Exception e) 
        {
            throw new ConnectionException(e.getMessage());
        }
    }
    
    public ArrayList<Product> getProductOfCategory(int categoryId) throws ConnectionException
    {
        ArrayList<Product> listProducts = new ArrayList<Product>();
        
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL =  "SELECT "
                                    + "APP.PRODUCT.PRODUCTID, "
                                    + "APP.PRODUCT.UNITARYPRICE, "
                                    + "APP.PRODUCT.PRODUCTNAME, "
                                    + "APP.PRODUCT.DESCRIPTION, "
                                    + "APP.PRODUCT.IMAGEPATH, "
                                    + "APP.PRODUCT.AVAILABLE "
                                + "FROM "
                                    + "APP.PRODUCT, "
                                    + "APP.CLASSEMENT, "
                                    + "APP.CATEGORY "
                                + "WHERE "
                                      + "APP.CATEGORY.CATEGORYID = ? "  
                                      + "AND APP.CLASSEMENT.CATEGORYID = APP.CATEGORY.CATEGORYID "
                                      + "AND APP.CLASSEMENT.PRODUCTID = APP.PRODUCT.PRODUCTID "
                                + "ORDER BY "
                                     + "APP.PRODUCT.PRODUCTNAME DESC";
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setInt(1, categoryId);
            ResultSet donnees = prepStat.executeQuery();
            
            while(donnees.next())
            {
               Product product = new Product(donnees.getInt("PRODUCTID"),
                                             donnees.getString("PRODUCTNAME"), 
                                             donnees.getDouble("UNITARYPRICE"), 
                                             donnees.getString("DESCRIPTION"),
                                             donnees.getString("IMAGEPATH"),
                                             donnees.getBoolean("AVAILABLE"));
               listProducts.add(product);
            }   
            
            connection.close();
            
            return listProducts;
        }
           
        catch(Exception e) 
        {
            throw new ConnectionException(e.getMessage());
        }
    }
    
    public Product getProduct(int productId) throws ConnectionException
    {
        Product product;

        
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL =  "SELECT "
                                    + "APP.PRODUCT.UNITARYPRICE, "
                                    + "APP.PRODUCT.PRODUCTNAME, "
                                    + "APP.PRODUCT.DESCRIPTION, "
                                    + "APP.PRODUCT.IMAGEPATH, "
                                    + "APP.PRODUCT.AVAILABLE, "
                                    + "APP.PRODUCT.PROMOTIONID "
                                + "FROM "
                                    + "APP.PRODUCT, "
                                    + "APP.PROMOTION "
                                + "WHERE "
                                      + "APP.PRODUCT.PRODUCTID = ? ";
            
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setInt(1, productId);
            ResultSet donnees = prepStat.executeQuery();
            donnees.next();
            
 
            product = new Product(productId,
                                  donnees.getString("PRODUCTNAME"), 
                                  donnees.getDouble("UNITARYPRICE"),
                                  donnees.getString("DESCRIPTION"),
                                  donnees.getString("IMAGEPATH"),
                                  donnees.getBoolean("AVAILABLE"));
            
            int promotionId = donnees.getInt("PROMOTIONID");
            
            if(promotionId != 0)
            {
                requeteSQL =  "SELECT "
                                    + "APP.PROMOTION.STARTDATE, "
                                    + "APP.PROMOTION.ENDDATE, "
                                    + "APP.PROMOTION.DISCOUNTPERCENTAGE, "
                                    + "APP.PROMOTION.QUANTITYOFFERED, "
                                    + "APP.PROMOTION.QUANTITYTOPURCHASE "
                                + "FROM "
                                    + "APP.PROMOTION "
                                + "WHERE "
                                      + "APP.PROMOTION.PROMOTIONID = ? ";
                
                prepStat = connection.prepareStatement(requeteSQL);
                prepStat.setInt(1, promotionId);
                donnees = prepStat.executeQuery();
                donnees.next();
            
                double discount = donnees.getDouble("DISCOUNTPERCENTAGE");
                GregorianCalendar startDate = new GregorianCalendar();
                startDate.setTime(donnees.getDate("STARTDATE"));
                GregorianCalendar endDate = new GregorianCalendar();
                endDate.setTime(donnees.getDate("ENDDATE"));
                
                if(discount != 0)   
                    product.setPromotion(new Promotion(startDate, 
                                                       endDate, 
                                                       discount));
                else
                    product.setPromotion(new Promotion(startDate, 
                                                       endDate, 
                                                       donnees.getInt("QUANTITYOFFERED"), 
                                                       donnees.getInt("QUANTITYTOPURCHASE")));
            }
            
            connection.close();
            
            return product;
        }   
                
        catch(Exception e) 
        {
            throw new ConnectionException(e.getMessage());
        }
    }
    
    public void login(String userId, String password) throws LoginException, ConnectionException
    {             
        try
        {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("jdbc/bricoweb");
            Connection connection = source.getConnection();
            String requeteSQL = "SELECT USERID FROM APP.BRICOWEBUSER WHERE USERID = ?";
            
            PreparedStatement prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setString(1, userId);
            ResultSet donnees = prepStat.executeQuery();
            
            boolean error[] = new boolean[2];
            error[0] = false;
            error[1] = false;
            
            if(!donnees.next())
            {
                error[0] = true;
                throw new LoginException(error);
            }
            
            requeteSQL = "SELECT PASSWORD FROM APP.BRICOWEBUSER WHERE USERID = ? AND PASSWORD = ?";
            
            prepStat = connection.prepareStatement(requeteSQL);
            prepStat.setString(1, userId);
            prepStat.setString(2, password);
            donnees = prepStat.executeQuery();
            
            if(!donnees.next())
            {
                error[1] = true;
                throw new LoginException(error);
            }
            
            connection.close();
        }
        catch(NamingException e)
        {
            throw new ConnectionException(e.getMessage().toString());
        }        
        catch(SQLException e)
        {
            throw new ConnectionException(e.getMessage().toString());
        }
    }
}
